/**
 * @Author: oldTea
 * @CreateTime: 2024-12-02
 * @Description:
 * @Version: 1.0
 */
package com.ambition.demo.dataBase;

/**
 * @Author: oldTea
 * @CreateTime: 2024-12-02
 * @Description:
 * @Version: 1.0
 */

import com.ambition.demo.dataBase.dao.SyncReceive1Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.*;

@Component
public class MergeStatementGenerator  {

    @Autowired
    SyncReceive1Dao syncReceiveDao;

    private static Map<String, String> TABLE_SAVE_DISK_COLUMN_MAP =new HashMap<>();
    private static Map<String, String> TABLE_UNIQUE_COLUMN_MAP = new HashMap<>();

    static{
        TABLE_UNIQUE_COLUMN_MAP.put("tb_dgs_base_unit","company_code,id");
        TABLE_SAVE_DISK_COLUMN_MAP.put("tb_dgs_base_unit", "sync_time");
    }

    /**
     * MERGE INTO employees AS target
     * USING (SELECT 1001 AS employee_id, 'John Doe' AS name, 'IT' AS department FROM DUAL) AS source
     * ON (target.employee_id = source.employee_id)
     * WHEN MATCHED THEN
     * UPDATE SET
     * target.name = source.name,
     * target.department = source.department
     * WHEN NOT MATCHED THEN
     * INSERT (employee_id, name, department)
     * VALUES (source.employee_id, source.name, source.department);
     */
    public static void main(String[] args) {
        List<Map<String, String>> dataList = getDataList(); // 假设这是你的数据源
        String tableName = "tb_dgs_base_unit";
        String conflict;
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("merge into ");
        sqlBuilder.append(tableName);
        sqlBuilder.append(" as target using (");
        int size = dataList.size();
        for (int i = 0; i < size; i++) {
            if (i != 0) {
                sqlBuilder.append("\nUNION ALL\n");
            }
            sqlBuilder.append(generateUsingClause(dataList.get(i)));
        }
        sqlBuilder.append(") as source on (");
        ArrayList<String> strings = new ArrayList<>(dataList.get(0).keySet());
        conflictDMSql(tableName,sqlBuilder,strings);
        String usingClause = sqlBuilder.toString();
        System.out.println(usingClause);
    }

    private static void conflictDMSql(String tableName, StringBuilder sqlBuilder, List<String> columnList) {

        if (TABLE_UNIQUE_COLUMN_MAP.containsKey(tableName)) {
            String uniqueColumn = TABLE_UNIQUE_COLUMN_MAP.get(tableName);
            String[] split = uniqueColumn.split(",");
            boolean first = true;

            StringBuilder builder = new StringBuilder();
            StringBuilder builder1 = new StringBuilder();
            StringBuilder builder2 = new StringBuilder();
            StringBuilder builder3 = new StringBuilder(" when not matched then insert (");
            StringBuilder builder4 = new StringBuilder(") values (");

            for(String column : columnList){
                if(!first){
                    builder3.append(",");
                }
                builder3.append(column);
                builder4.append("source.").append("\"").append(column).append("\"");
                first = false;
            }
            builder4.append(");");
            first = true;
            for(String string : split){
                if (!first) {
                    builder.append(" and ");
                }
                if(columnList.contains(string)){
                    columnList.remove(string);
                }
                builder.append("target.").append(string).append("=").append("source.").append(string);
                first = false;
            }

            first = true;
            builder.append(") when matched then update set ");
            for(String column : columnList){
                if(!first){
                    builder1.append(",");
                }
                builder1.append("target.\"").append(column).append("\"=").append("source.\"").append(column).append("\"");
                first = false;
            }

            sqlBuilder.append(builder);
            sqlBuilder.append(builder1);
            sqlBuilder.append(builder2);
            sqlBuilder.append(builder3);
            sqlBuilder.append(builder4);
        }

    }

    private static String generateUsingClause(Map<String, String> record) {
        StringBuilder clause = new StringBuilder("SELECT ");
        boolean first = true;
        for (Map.Entry<String, String> entry : record.entrySet()) {
            if (!first) {
                clause.append(", ");
            }
            if(entry.getKey().equals("deleted")){
                String value = entry.getValue();
                if(value.equals("t")){
                    entry.setValue("1");
                }else {
                    entry.setValue("0");
                }
            }
            if("null".equals(entry.getValue())){
                clause.append(entry.getValue()).append(" AS ").append(entry.getKey());
            }else {
                clause.append("'").append(entry.getValue()).append("' AS ").append(entry.getKey());
            }
            first = false;
        }
        return clause.toString();
    }

    // 模拟获取数据列表的方法
    private static List<Map<String, String>> getDataList() {
        List<Map<String, String>> dataList = new ArrayList<>();
        Map<String, String> record1 = new HashMap<>();
        record1.put("employee_id", "1001");
        record1.put("name", "John Doe");
        record1.put("department", "IT");
        record1.put("company_code", "123");
        record1.put("deleted", "t");
        record1.put("age","null");
        dataList.add(record1);
        Map<String, String> record2 = new HashMap<>();
        record2.put("employee_id", "1002");
        record2.put("name", "John Doe2");
        record2.put("department", "IT2");
        record2.put("company_code", "456");
        record2.put("deleted", "f");
        record2.put("age","null");
        dataList.add(record1);

        // 添加更多的记录...
        return dataList;
    }

    //@Override
    public void afterPropertiesSet() throws Exception {
        List<SyncReceiveConfig> list = syncReceiveDao.test();
        System.out.println(list);
    }
}

